﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Manage_Pupils.Controller
{
    
    class addScoreController
    {
        Connection cons = new Connection();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand command = new SqlCommand();

        public addScoreController() {
            cons.Connect();
            command.Connection = cons.con;
            command.CommandType = CommandType.Text;
        }

        public DataTable getData() {
            dt.Clear();
            cons.Connect();
            command.Connection = cons.con;
            //command.CommandType = CommandType.Text;
            command.CommandText = "Select * from BangDiem";
            da.SelectCommand = command;
            da.Fill(dt);

            return dt;
        
        }

        public void getDataScore(ComboBox cbCodeSubject, ComboBox cbCodePupils,ComboBox cbCodeSemester, ComboBox cbClass) {
            cons.Connect();

            //command.Connection = cons.con;
            //command.CommandType = CommandType.Text;
            //CB Ma Mon hoc
            DataTable dtMH = new DataTable();

            command.CommandType = CommandType.Text;
            command.CommandText = "Select MaMH from MonHoc";
            da.SelectCommand = command;
            da.Fill(dtMH);

            cbCodeSubject.DataSource = dtMH;
            cbCodeSubject.DisplayMember = "MaMH";
            cbCodeSubject.ValueMember = "MaMH";
            cbCodeSubject.SelectedValue = "MaMH";

            //cb Ma hoc ky
            DataTable dtSemester = new DataTable();
            command.CommandText = "select MaHK from HocKi";
            da.SelectCommand = command;
            da.Fill(dtSemester);

            cbCodeSemester.DataSource = dtSemester;
            cbCodeSemester.DisplayMember = "MaHK";
            cbCodeSemester.ValueMember = "MaHK";
            cbCodeSemester.SelectedValue = "MaHK";

            //cb Ma Hoc Sinh
            DataTable dtMAHS = new DataTable();
            command.CommandText = "Select MaHS from HocSinh";
            da.SelectCommand = command;
            da.Fill(dtMAHS);

            cbCodePupils.DataSource = dtMAHS;
            cbCodePupils.DisplayMember = "MaHS";
            cbCodePupils.ValueMember = "MaHS";
            cbCodePupils.SelectedValue = "MaHS";

            //cb Class
            DataTable dtClass = new DataTable();
            command.CommandText = "Select TenLop from Lop";
            da.SelectCommand = command;
            da.Fill(dtClass);

            cbClass.DataSource = dtClass;
            cbClass.DisplayMember = "TenLop";
            cbClass.ValueMember = "TenLop";
            cbClass.SelectedValue = "TenLop";

            // Disconnect
            cons.DisConnect();
        }


        //add from table get component on form
        public void addScore(ComboBox cbCodeSubject, ComboBox cbCodePupils, ComboBox cbCodeSemester, TextBox tbMidTerm, TextBox tbEndofPeriod, ComboBox cbClass, TextBox tbMedium)
        {
            cons.Connect();
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = cons.con;
            commandInsert.CommandType = CommandType.Text;

            //command.Connection = cons.con;
            
            //command.CommandType = CommandType.Text;
            //command.CommandText = "Select * from BangDiem";
            //da.SelectCommand = command;
            //da.Fill(dt);
            dt = getData();

            dt.Clear();

            DataRow row = dt.NewRow();
            row["MaMH"] = cbCodeSubject.Text;
            row["MaHS"] = cbCodePupils.Text;
            row["MaHK"] = cbCodeSemester.Text;
            // bat loi giua ky
           try
            {
                row["DiemGK"] = tbMidTerm.Text;
           }
           catch (Exception )
           {
                MessageBox.Show("Lỗi nhập điểm giữa kỳ!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               return;
           }
            // bat loi cuoi ky
            try
            {
               
                row["DiemCK"] = tbEndofPeriod.Text;
            }
            catch (Exception)
           {
                MessageBox.Show("Lỗi nhập điểm cuối kỳ!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }



           
            row["Lop"] = cbClass.Text;
            row["TrungBinhHK"] = Math.Round(((float.Parse(tbMidTerm.Text) + float.Parse(tbEndofPeriod.Text)*2)/3),2,MidpointRounding.AwayFromZero);
            dt.Rows.Add(row);

            commandInsert.CommandText = @"Insert BangDiem(MaMH,MaHS,MaHK,DiemGK,DiemCK,Lop,TrungBinhHK)
                                            Values(@MaMH, @MaHS, @MaHK, @DiemGK, @DiemCK, @Lop, @TrungBinhHK)";
            commandInsert.Parameters.Add("@MaMH", SqlDbType.NChar,10, "MaMH");
            commandInsert.Parameters.Add("@MaHS", SqlDbType.NChar, 10, "MaHS");
            commandInsert.Parameters.Add("@MaHK",SqlDbType.NChar,10,"MaHK");
            commandInsert.Parameters.Add("@DiemGK", SqlDbType.Float, 5, "DiemGK");
            commandInsert.Parameters.Add("@DiemCK", SqlDbType.Float,5,"DiemCK");
            commandInsert.Parameters.Add("@Lop", SqlDbType.NChar, 10, "Lop");
            commandInsert.Parameters.Add("@TrungBinhHK", SqlDbType.Float, 5, "TrungBinhHK");

            da.InsertCommand = commandInsert;
            da.Update(dt);
            MessageBox.Show("Score has added successful!","Success",MessageBoxButtons.OK);
            
        }

        // add from excel
        public void addFromExcel(String path) {

            cons.Connect();
            DataTable dtFile = Manage_Pupils.Controller.Import.Query(@path);
           // dv.DataSource = dtFile;
            //MessageBox.Show(dtFile.Rows.Count.ToString());
            //MessageBox.Show(dtFile.Columns.Count.ToString());
           
                for (int i = 0; i < dtFile.Rows.Count; i++)
                {
                    command = cons.con.CreateCommand();
                    command.CommandText = "INSERT INTO BangDiem values(@MaMH, @MaHS, @MaHK, @DiemGK, @DiemCK, @Lop, @TrungBinhHK)";
                    command.Parameters.AddWithValue("@MaMH", dtFile.Rows[i][0].ToString());
                    command.Parameters.AddWithValue("@MaHS", dtFile.Rows[i][1].ToString());
                    command.Parameters.AddWithValue("@MaHK", dtFile.Rows[i][2].ToString());
                    command.Parameters.AddWithValue("@DiemGK", dtFile.Rows[i][3]);
                    command.Parameters.AddWithValue("@DiemCK", dtFile.Rows[i][4]);
                    command.Parameters.AddWithValue("@Lop", dtFile.Rows[i][5].ToString());
                    command.Parameters.AddWithValue("@TrungBinhHK", dtFile.Rows[i][6]);
                    try
                    {
                    command.ExecuteNonQuery();
                             }
                    catch (Exception) {
                        MessageBox.Show("Lỗi nhập điểm!","Lỗi",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    }
                }
           

                cons.DisConnect();

        }

        //Edit Score
        public void edit(ComboBox cbCodeSubject,ComboBox cbCodePupils,ComboBox cbCodeSemester, TextBox tbMidTerm, TextBox tbEndofPeriod, ComboBox cbClass, TextBox tbMedium, DataTable dt) {
            
            //cons.Connect();
           // dt = getData();
            //dt.Clear();
            
            cons.Connect();
            SqlCommand commandUpdate = new SqlCommand();
            commandUpdate.Connection = cons.con;
            commandUpdate.CommandType = CommandType.Text;
   
           /* DataRow row = dt.Select("MaMH=\'" + (cbCodeSubject.Text) + "\'")[0];
            row.BeginEdit();
            
            //row["MaHS"] = cbCodePupils.Text;
            row["MaHK"] = cbCodeSemester.Text;
            row["DiemGK"] = float.Parse(tbMidTerm.Text);
            row["DiemCK"] = float.Parse(tbEndofPeriod.Text);
            row["Lop"] = cbClass.Text;
            row["TrungBinhHK"] = Math.Round(((float.Parse(tbMidTerm.Text) + float.Parse(tbEndofPeriod.Text) * 2) / 3), 2, MidpointRounding.AwayFromZero);
            row.EndEdit();
            * */
       
            
            commandUpdate.CommandText = @"Update BangDiem Set 
                                                        MaHK=@MaHK,
                                                        DiemGK=@DiemGK,
                                                        DiemCK=@DiemCK,
                                                        Lop=@Lop,
                                                        TrungBinhHK=@TrungBinhHK
                                                Where MaMH=@MaMH and MaHS = @MaHS";
            commandUpdate.Parameters.Add("@MaMH", SqlDbType.NChar, 10, "MaMH");
            commandUpdate.Parameters.Add("@MaHS", SqlDbType.NChar, 10, "MaHS");
            commandUpdate.Parameters.Add("@MaHK", SqlDbType.NChar, 10, "MaHK");
            commandUpdate.Parameters.Add("@DiemGK", SqlDbType.Float, 5, "DiemGK");
            commandUpdate.Parameters.Add("@DiemCK", SqlDbType.Float, 5, "DiemCK");
            commandUpdate.Parameters.Add("@Lop", SqlDbType.NChar, 10, "Lop");
            commandUpdate.Parameters.Add("@TrungBinhHK", SqlDbType.Float, 5, "TrungBinhHK");


            da.UpdateCommand = commandUpdate;
            da.Update(dt);
           // dt.Clear();
            //dt = getData();

            MessageBox.Show("Update successful");


        }

        // del
        public void delete() { 
            
        }

        //get path()
        public String getPath() {
            OpenFileDialog ofd = new OpenFileDialog();
            String path = ofd.ShowDialog() == DialogResult.OK ? ofd.FileName : "";
            return path;
        }
        public void binding(ComboBox cbCodeSubject, ComboBox cbCodePupils, ComboBox cbCodeSemester, TextBox tbMidTerm, TextBox tbEndofPeriod, ComboBox cbClass, TextBox tbMedium, DataTable dt)
        {
            cbCodeSubject.DataBindings.Clear();
            cbCodeSubject.DataBindings.Add("Text", dt, "MaMH");
            cbCodePupils.DataBindings.Clear();
            cbCodePupils.DataBindings.Add("Text", dt, "MaHS");
            cbCodeSemester.DataBindings.Clear();
            cbCodeSemester.DataBindings.Add("Text", dt, "MaHK");
            tbMidTerm.DataBindings.Clear();
            tbMidTerm.DataBindings.Add("Text", dt, "DiemGK");
            tbEndofPeriod.DataBindings.Clear();
            tbEndofPeriod.DataBindings.Add("Text", dt, "DiemCK");
            cbClass.DataBindings.Clear();
            cbClass.DataBindings.Add("Text", dt, "Lop");
            tbMedium.DataBindings.Clear();
            tbMedium.DataBindings.Add("Text", dt, "TrungBinhHK");
        }
    }
}
